<?
$SQL="select 
	r.id,r.reseller,r.firstname,r.lastname,r.address1,r.address2,r.city,r.state,r.zip,r.country, sum(price-real_price) suma,payment_date
from resellers r left join sales s on(r.id=s.id)
where r.country='US' and payed='true' 
group by r.id,s.payment_date
";
$SQL="SELECT rl.parent_id,
	r.id,r.reseller,r.firstname,r.lastname,r.address1,r.address2,r.city,r.state,r.zip,r.country,payment_date,ref_payment_date,link_type,
	((s.price-s.real_price)*(rl.link_type='union')*(s.payed='true')+s.real_price*0.1*(rl.link_type='affiliate')*(s.type='plan')*(s.ref_payed='true')) as suma
FROM resellers r
LEFT JOIN reseller_links rl on(r.id=rl.parent_id)
LEFT JOIN sales s on(s.id=rl.child_id)

WHERE  r.country='US'
having suma>0;
;";
echo $SQL;


if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh))) return 0;
while($row=mysql_fetch_assoc($result)){
	$flag=0;
	if($row[link_type]=='affiliate'){
		
		if($row[ref_payment_date]<'2004-01-00'){
			$resellers[$row[id]][payments][$row[ref_payment_date]]+=$row[suma];
			$resellers[$row[id]][total]+=$row[suma];
			$flag=1;
		}
	}else {
		if($row[payment_date]<'2004-01-00'){
			$resellers[$row[id]][payments][$row[payment_date]]+=$row[suma];
			$resellers[$row[id]][total]+=$row[suma];
			$flag=1;
		}
	}
	if($flag){
		$resellers[$row[id]][reseller]=$row[reseller];
		$resellers[$row[id]][firstname]=$row[firstname];
		$resellers[$row[id]][lastname]=$row[lastname];
		$resellers[$row[id]][address1]=$row[address1];
		$resellers[$row[id]][address2]=$row[address2];
		$resellers[$row[id]][city]=$row[city];
		$resellers[$row[id]][state]=$row[state];
		$resellers[$row[id]][zip]=$row[zip];
		$resellers[$row[id]][country]=$row[country];

	}
}

?>

<table class=bTable width=700 cellspacing=0 cellpadding=0>
	<tr>
		<th>#</th>
		<th>reseller</th>
		<th>copy/paste details</th>
		<th>payments</th>
	</tr>
	<?foreach($resellers as $r){?>
	<tr>
		<td valign=top><?=++$count?></td>
		<td valign=top><?=$r[reseller]?></td>
		<td valign=top><?="$r[firstname] $r[lastname]<br>"."$r[address1]<br>";if($r[address2]) echo "$r[address2]<br>";echo "$r[city], $r[state] $r[zip]<br> ".$r[country];?></td>
		<td>
			<table width=100% height=100% cellspacing=0 cellpadding=0>
			<?
			foreach($r[payments] as $k=>$v){
				echo "<tr><td>$k</td><td>".sprintf("%01.2f", $v)."</td></tr>";	
			}
			?>
			<tr><th>total:</th><th><?=sprintf("%01.2f", $r[total])?></th></tr>
			</table>				
		</td>
	</tr>
	<?}?>
</table>
